<!DOCTYPE html>
<html lang="en">

<!-- Head tag (contains Google-Analytics、Baidu-Tongji)-->
<head>
  <!-- Google Analytics -->
  

  <!-- Baidu Tongji -->
  
    <script type="text/javascript">
      // Originial
      var _hmt = _hmt || [];
      (function () {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?cdf616a127124dd7ff798963125b304d";
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
  

  <!-- Baidu Push -->
  
    <script>
      (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
          bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
          bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
      })();
    </script>
  

  <meta charset="utf-8"/>
  <meta http-equiv="X-UA-Compatible" content="IE=edge"/>

  <meta name="google-site-verification" content="lxDfCplOZbIzjhG34NuQBgu2gdyRlAtMB4utP5AgEBc"/>
  <meta name="baidu-site-verification" content="PpzM9WxOJU"/>

  <meta name="viewport" content="width=device-width, initial-scale=1"/>
  <meta name="description" content="It&#39;s an IT blog..."/>
  <meta name="keyword" content="zhuang,IT  blog,Blog"/>
  <link rel="shortcut icon" href="/img/avatar/java-head.png"/>

  <!-- Place this tag in your head or just before your close body tag. -->
  <script async="async" defer="defer" src="https://buttons.github.io/buttons.js"></script>

  
    <!-- Bootstrap Core CSS -->
    <link rel="stylesheet" href="/css/bootstrap.min.css"/>

    <!-- Custom CSS -->
    <link rel="stylesheet" href="/css/beantech.min.css"/>

    <!-- Pygments Highlight CSS -->
    <link rel="stylesheet" href="/css/highlight.css"/>
    <link rel="stylesheet" href="/css/widget.css"/>
    <link rel="stylesheet" href="/css/rocket.css"/>
    <link rel="stylesheet" href="/css/signature.css"/>
    <link rel="stylesheet" href="/css/catalog.css"/>
    <link rel="stylesheet" href="/css/livemylife.css"/>

    
      <!-- wave start -->
      <link rel="stylesheet" href="/css/wave.css"/>
      <!-- wave end -->
    

    
      <!-- top start (article top hot config) -->
      <link rel="stylesheet" href="/css/top.css"/>
      <!-- top end -->
    

    
      <!-- ThemeColor start -->
      <link rel="stylesheet" href="/css/scroll.css"/>
      <!-- ThemeColor end -->
    

    
      <!-- viewer start (Picture preview) -->
      <link rel="stylesheet" href="/css/viewer.min.css"/>
      <!-- viewer end -->
    

    
      <!-- Search start -->
      <link rel="stylesheet" href="/css/search.css"/>
      <!-- Search end -->
    

    
      <!-- ThemeColor start -->
      <link rel="stylesheet" href="/css/themecolor.css"/>
      <!-- ThemeColor end -->
    

    

    
  

  <!-- Custom Fonts -->
  <!-- <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css" rel="stylesheet" type="text/css"> -->
  <!-- Hux change font-awesome CDN to qiniu -->
  <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/4.5.0/css/font-awesome.min.css" type="text/css">
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">

  <!-- Hux Delete, sad but pending in China <link href='http://fonts.googleapis.com/css?family=Lora:400,700,400italic,700italic' rel='stylesheet' type='text/css'> <link
  href='http://fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,800italic,400,300,600,700,800' rel='stylesheet' type='text/ css'> -->

  <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
  <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
  <!--[if lt IE 9]> <script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script> <script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script> <![endif]-->

  <!-- ga & ba script hoook -->
  <link rel="canonical" href="http://yoursite-url/cn/mysql/MySQL索引深入剖析/">
  <title>
    
      MySQL索引深入剖析 - either thin | or die
    
  </title>
<meta name="generator" content="Hexo 5.4.0"></head>


<!-- hack iOS CSS :active style -->

	<body ontouchstart="" class="body--light body--dark">


		<!-- ThemeColor -->
		
		<!-- ThemeColor -->
<style type="text/css">
  .body--light {
    --light-mode: none;
    --dark-mode: block;
  }
  .body--dark {
    --light-mode: block;
    --dark-mode: none;
  }
  i.mdui-icon.material-icons.light-mode {
    display: var(--light-mode);
  }
  i.mdui-icon.material-icons.dark-mode {
    display: var(--dark-mode);
  }
</style>
<div class="toggle" onclick="document.body.classList.toggle('body--dark')">
  <i class="mdui-icon material-icons light-mode"></i>
  <i class="mdui-icon material-icons dark-mode"></i>
</div>
<script>
  //getCookieValue
  function getCookieValue(a) {
    var b = document.cookie.match('(^|[^;]+)\\s*' + a + '\\s*=\\s*([^;]+)');
    return b
      ? b.pop()
      : '';
  }
  let themeMode = 'dark';
  if (getCookieValue('sb-color-mode') && (getCookieValue('sb-color-mode') !== themeMode)) {
    let dbody = document.body.classList;
    themeMode === 'dark' ? dbody.remove('body--dark') : dbody.add('body--dark');
  }

  //setCookieValue
  var toggleBtn = document.querySelector(".toggle");
  toggleBtn.addEventListener("click", function () {
    var e = document.body.classList.contains("body--dark");
    var cookieString = e
      ? "dark"
      : "light";
    var exp = new Date();
    exp.setTime(exp.getTime() + 3 * 24 * 60 * 60 * 1000); //3天过期
    document.cookie = "sb-color-mode=" + cookieString + ";expires=" + exp.toGMTString() + ";path=/";
  });
</script>

		

		<!-- Gitter -->
		

		<!-- Navigation (contains search)-->
		<!-- Navigation -->
<nav class="navbar navbar-default navbar-custom navbar-fixed-top">
  <div class="container-fluid">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header page-scroll">
      <button type="button" class="navbar-toggle">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
      <a class="navbar-brand" href="/">平平淡淡</a>
    </div>

    <!-- Collect the nav links, forms, and other content for toggling -->
    <!-- Known Issue, found by Hux: <nav>'s height woule be hold on by its content. so, when navbar scale out, the <nav> will cover tags. also mask any touch event of tags, unfortunately. -->
    <div id="huxblog_navbar">
      <div class="navbar-collapse">
        <ul class="nav navbar-nav navbar-right">
          <li>
            <a href="/">首页</a>
          </li>

          
          
          
          
          <li>
            <a href="/archive/">
              
              归档
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/tags/">
              
              标签
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/categories/">
              
              分类
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/about/">
              
              关于
              
              
            </a>
          </li>
          
          

          
          <li>
            <a class="popup-trigger">
              <span class="search-icon"></span>搜索</a>
          </li>
          

          <!-- LangSelect -->
          
        </ul>
      </div>
    </div>
    <!-- /.navbar-collapse -->
  </div>
  <!-- /.container -->
</nav>
<!-- progress -->
<div id="progress">
  <div class="line" style="width: 0%;"></div>
</div>

<script>
  // Drop Bootstarp low-performance Navbar Use customize navbar with high-quality material design animation in high-perf jank-free CSS3 implementation
  var $body = document.body;
  var $toggle = document.querySelector('.navbar-toggle');
  var $navbar = document.querySelector('#huxblog_navbar');
  var $collapse = document.querySelector('.navbar-collapse');

  $toggle.addEventListener('click', handleMagic)

  function handleMagic(e) {
    if ($navbar.className.indexOf('in') > 0) {
      // CLOSE
      $navbar.className = " ";
      // wait until animation end.
      setTimeout(function() {
        // prevent frequently toggle
        if ($navbar.className.indexOf('in') < 0) {
          $collapse.style.height = "0px"
        }
      }, 400)
    } else {
      // OPEN
      $collapse.style.height = "auto"
      $navbar.className += " in";
    }
  }
</script>


		<!-- Post Header (contains intro-header、signature、wordcount、busuanzi、waveoverlay) -->
		<!-- Modified by Yu-Hsuan Yen -->
<!-- Post Header -->

  <style type="text/css">
    .body--light {
      /* intro-header */
      --intro-header-background-image-url-home: url('/img/header_img/home_bg1.jpg');
      --intro-header-background-image-url-post: url('/img/header_img/lml_bg.jpg');
      --intro-header-background-image-url-page: url('//img/header_img/lml_bg.jpg');
    }
    .body--dark {
      --intro-header-background-image-url-home: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('/img/header_img/home_bg1.jpg');
      --intro-header-background-image-url-post: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('/img/header_img/lml_bg.jpg');
      --intro-header-background-image-url-page: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('//img/header_img/lml_bg.jpg');
    }

    header.intro-header {
       /*post*/
        background-image: var(--intro-header-background-image-url-post);
        /* background-image: url('/img/header_img/lml_bg.jpg'); */
      
    }

    
      #signature {/*signature*/
        background-image: url('/img/signature/signature.png');
      }
    
  </style>





<header class="intro-header">
  <!-- Signature -->
  <div id="signature">
    <div class="container">
      <div class="row">
        <div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
          
          <div class="post-heading">
            <div class="tags">
              
              <a class="tag" href="/tags/#数据库" title="数据库">数据库</a>
              
              <a class="tag" href="/tags/#mysql" title="mysql">mysql</a>
              
            </div>
            <h1>MySQL索引深入剖析</h1>
            <h2 class="subheading"></h2>
            <span class="meta">
              Posted by zhuang on
              2020-04-20
            </span>


            
            <!-- WordCount start -->
            <div class="blank_box"></div>
            <span class="meta">
              Estimated Reading Time <span class="post-count">11</span> Minutes
            </span>
            <div class="blank_box"></div>
            <span class="meta">
              Words <span class="post-count">3.3k</span> In Total
            </span>
            <div class="blank_box"></div>
            <!-- WordCount end -->
            
            
            <!-- 不蒜子统计 start -->
            <span class="meta" id="busuanzi_container_page_pv">
              Viewed <span id="busuanzi_value_page_pv"><i class="fa fa-spinner fa-spin"></i></span> Times
            </span>
            <!-- 不蒜子统计 end -->
            


          </div>
          
        </div>
      </div>
    </div>
  </div>

  
  <!-- waveoverlay start -->
  <div class="preview-overlay">
    <svg class="preview-waves" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 24 150 28" preserveAspectRatio="none" shape-rendering="auto">
      <defs>
        <path id="gentle-wave" d="M-160 44c30 0 58-18 88-18s 58 18 88 18 58-18 88-18 58 18 88 18 v44h-352z"></path>
      </defs>
      <g class="preview-parallax">
        <use xlink:href="#gentle-wave" x="48" y="0" fill=var(--gentle-wave1)></use>
        <use xlink:href="#gentle-wave" x="48" y="3" fill=var(--gentle-wave2)></use>
        <use xlink:href="#gentle-wave" x="48" y="5" fill=var(--gentle-wave3)></use>
        <use xlink:href="#gentle-wave" x="48" y="7" fill=var(--gentle-wave)></use>
      </g>
    </svg>
  </div>
  <!-- waveoverlay end -->
  

</header>



		<!-- Main Content (Post contains
	Pager、
	tip、
	socialshare、
	gitalk、gitment、disqus-comment、
	Catalog、
	Sidebar、
	Featured-Tags、
	Friends Blog、
	anchorjs、
	) -->
		<!-- Modify by Yu-Hsuan Yen -->
<!-- Post Content -->
<article>
  <div class="container">
    <div class="row">
      <!-- Post Container -->
      <div class="col-lg-8 col-lg-offset-1 col-md-10 col-md-offset-1 post-container">

        <h2 id="1-索引是什么？"><a href="#1-索引是什么？" class="headerlink" title="1. 索引是什么？"></a>1. 索引是什么？</h2><h3 id="1-1-索引"><a href="#1-1-索引" class="headerlink" title="1.1 索引"></a>1.1 索引</h3><p>数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询，更新数据表中的数据。</p>
<p><img src="image-20200420132943147.png" alt="image-20200420132943147"></p>
<p>数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据（循环调用存储引擎的读取下一行数据的接口），直到找到这条数据。</p>
<p>但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，找到数据存放的磁盘地址以后，就可以拿到数据了。</p>
<h3 id="1-2-索引类型"><a href="#1-2-索引类型" class="headerlink" title="1.2 索引类型"></a>1.2 索引类型</h3><h2 id="2-索引存储模型推演"><a href="#2-索引存储模型推演" class="headerlink" title="2. 索引存储模型推演"></a>2. 索引存储模型推演</h2><h3 id="2-1-二叉查找树（BST-Binary-Search-Tree）"><a href="#2-1-二叉查找树（BST-Binary-Search-Tree）" class="headerlink" title="2.1 二叉查找树（BST Binary Search Tree）"></a>2.1 二叉查找树（BST Binary Search Tree）</h3><p>二叉查找树的左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。</p>
<p><img src="image-20200420133924747.png" alt="image-20200420133924747"></p>
<p>二叉查找树即能够实现快速查找，又能够实现快速插入。</p>
<p>缺点：它的查找耗时与树的深度相关，最坏的情况下时间复杂度退为O(n).</p>
<p>比如：我们插入的数据刚好是由序的，2,6,11,13,17,22.</p>
<p>它会变成链表（斜树），造成倾斜的原因是：左右子树深度差太多，不够平衡</p>
<p><img src="image-20200420134240791.png" alt="image-20200420134240791"></p>
<h3 id="2-2-平衡二叉数（AVL-Tree）（左旋、右旋）"><a href="#2-2-平衡二叉数（AVL-Tree）（左旋、右旋）" class="headerlink" title="2.2 平衡二叉数（AVL Tree）（左旋、右旋）"></a>2.2 平衡二叉数（AVL Tree）（左旋、右旋）</h3><p>平衡二叉数的定义：左右子树深度差绝对值不能超过1</p>
<p>按顺序插入1、2、3、4、5、6</p>
<p><img src="image-20200420134805646.png" alt="image-20200420134805646"></p>
<p>平衡是怎么做到的呢？怎么保证左右子树的深度差不能超过1呢？</p>
<p>插入1、2、3</p>
<p>因为它是右节点下面接一个右节点，右右型，所以这个时候把2 提上去，这个操作叫做左旋</p>
<p><img src="image-20200421103738728.png" alt="image-20200421103738728"></p>
<p>同样，如果我们插入7、6、5，这个时候会变成左左型，就会发生右旋操作，把6提上去</p>
<p><img src="image-20200421103848471.png" alt="image-20200421103848471"></p>
<p>所以为了保持平衡，AVL树在插入和更新数据的时候执行了一系列的计算和调整的操作。</p>
<p>平衡二叉数作为索引怎么查询数据？</p>
<p>一个节点，作为索引应该存储三块内容：</p>
<p>第一个是索引的键值。比如我们在id上面创建一个索引，在用where id = 1的条件查询的时候就会找到索引里面的id的这个键值。</p>
<p>第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放地址</p>
<p>第三个是左右子节点的引用。</p>
<p><img src="image-20200421104241650.png" alt="image-20200421104241650"></p>
<p>问题：</p>
<p>当我们用树的结构来存储索引的时候，因为拿到一块数据就要在Server层比较是不是需要的数据，如果不是就要再读一次磁盘。访问一个节点就要跟磁盘之间发生一次IO。InnoDB操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是16K。</p>
<p>那么，一个树的节点就是16K的大小。</p>
<p>如果我们一个节点只存一个键值+数据+引用，远远达不到16K的容量，所以访问一个树节点，进行一次IO的时候，浪费了大量的空间。</p>
<p>如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多，消耗的时间就越多。</p>
<p>解决方案：</p>
<p>第一：让每个节点存储更多的数据</p>
<p>第二：节点上的关键字的数量越多，我们的指针数越多，意味着可以有更多的分叉（路数），分叉数越多，树的深度就会减少。</p>
<p>这个时候，我们的树就不再是二叉，而是多叉，或者叫做多路。</p>
<h3 id="2-3-多路平衡查找树（B-Tree）（分裂、合并）"><a href="#2-3-多路平衡查找树（B-Tree）（分裂、合并）" class="headerlink" title="2.3 多路平衡查找树（B Tree）（分裂、合并）"></a>2.3 多路平衡查找树（B Tree）（分裂、合并）</h3><p>Balanced Tree就是多路平衡查找树，叫做B Tree（B代表平衡）</p>
<p>跟AVL树一样，B树在枝节点和叶子节点存储键值、数据地址、节点引用。</p>
<p>它有一个特点：分叉数（路数）永远比关键字数多1。每个节点存储两个关键字，那么就会有三个指针指向三个子节点。</p>
<p><img src="image-20200421111544149.png" alt="image-20200421111544149"></p>
<p>B Tree是怎么实现一个节点存储多个关键字，还保持平衡的呢？更AVL树有什么区别？</p>
<p>比如Max Degree（路数）是3的时候，我们插入1、2、3，在插入3的时候，本来应该在第一个磁盘，但是如果一个节点有三个关键字的时候，意味着有4个指针，子节点会变成4路，所以这个时候必须进行分裂。把中间的数据2提上去，把1和3变成2的子节点。</p>
<p>如果删除节点，会有相反的的合并的操作。</p>
<p><img src="image-20200421131708478.png" alt="image-20200421131708478"></p>
<p><img src="cheshi21.gif" alt=""></p>
<p>节点的分裂和合并，其实就是InnoDB页（page）的分裂和合并</p>
<h3 id="2-4-B-树（加强版多路平衡查找树）"><a href="#2-4-B-树（加强版多路平衡查找树）" class="headerlink" title="2. 4 B+树（加强版多路平衡查找树）"></a>2. 4 B+树（加强版多路平衡查找树）</h3><p>InnoDB里面的B+树的存储结构</p>
<p><img src="image-20200421132322018.png" alt="image-20200421132322018"></p>
<p>MySQL中的B+Tree有几个特点：</p>
<ul>
<li>它的关键字的数量跟路数相等；</li>
<li>B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点存储数据</li>
</ul>
<p>搜索关键字不会直接返回，会到最后一层的叶子节点。如果搜索id=28，虽然在第一层直接命中了，但是数据地址在叶子节点上，所以还要继续往下搜索，一直到叶子节点。</p>
<ul>
<li>B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。</li>
</ul>
<p>InnoDB中B+Tree特点带来的优势：</p>
<ul>
<li>它是B Tree的变种，B tree能解决的问题（每个节点存储更多的关键字；路数更多），它都能解决。 </li>
<li>扫库、扫表能力更强（对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵B+Tree拿到所有的数据）</li>
<li>B+Tree的磁盘读写能力相对于B Tree来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键词更多）</li>
<li>排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）</li>
<li>效率更加稳定（B+Tree 永远是在叶子节点上拿到数据，所以IO次数是稳定的）</li>
</ul>
<h3 id="2-5-索引方式：真的是用的B-Tree吗？"><a href="#2-5-索引方式：真的是用的B-Tree吗？" class="headerlink" title="2.5 索引方式：真的是用的B+Tree吗？"></a>2.5 索引方式：真的是用的B+Tree吗？</h3><p>创建索引，索引方式有两种：Hash、BTREE。</p>
<p>Hash：以KV的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据。</p>
<p><img src="image-20200421145114365.png" alt="image-20200421145114365"></p>
<p>哈希索引有什么特点：</p>
<ul>
<li>哈希查询速度快，但不是按顺序存储，不能用于排序</li>
<li>在查询数据的时候要根据键值计算哈希码，只能支持等值查询，不支持范围查询</li>
<li>如果字段重复值多，会出现大量的哈希冲突，效率减低</li>
<li>InnodDB，不能显示创建一个哈希索引（所谓的支持哈希索引），memory存储引擎可以使用Hash索引</li>
</ul>
<h2 id="3-B-Tree落地形式"><a href="#3-B-Tree落地形式" class="headerlink" title="3. B+Tree落地形式"></a>3. B+Tree落地形式</h2><p>InnoDB的表有两个文件（.frm和.ibd），MyISAM的表有三个文件(.frm、.MYD、.MYI)</p>
<h3 id="3-1-MyISAM"><a href="#3-1-MyISAM" class="headerlink" title="3.1 MyISAM"></a>3.1 MyISAM</h3><p>一个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录。</p>
<p>一个是.MYI文件，I代表Index，是MyISAM的索引文件，存放索引。</p>
<p>在MyISAM里面，索引和数据是两个独立的文件。</p>
<p>MyISAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址，从索引未见.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。</p>
<p><img src="image-20200421160748078.png" alt="image-20200421160748078"></p>
<h3 id="3-2-InnoDB"><a href="#3-2-InnoDB" class="headerlink" title="3.2 InnoDB"></a>3.2 InnoDB</h3><p>在InnoDB里面，是以主键为索引来组织数据的存储的，所以索引文件和数据文件时同一个文件，都在.ibd里面。</p>
<p>在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据，</p>
<p><img src="image-20200421161011709.png" alt="image-20200421161011709"></p>
<p>聚集索引（聚簇索引）：就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）</p>
<p>在InnoDB里面，它组织数据的方式叫做（聚集）索引组织表，所以主键索引是聚集索引，非主键的都是非聚集索引。</p>
<p>主键之外的索引，比如在name字段上面建的普通索引，又是怎么存储和检索数据的呢？</p>
<p><img src="image-20200421183830628.png" alt="image-20200421183830628"></p>
<p>InnoDB中，主键索引和辅助索引是有一个主次之分。</p>
<p>辅助索引存储的是辅助索引和主键值，如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。</p>
<p>比如用name索引查询name=“Qingshan”，它会在叶子节点找到主键值，也就是id=1，然后在到主键索引的叶子节点拿到数据。</p>
<p>如果一张表没有主键怎么办？</p>
<ol>
<li>如果我们定义主键（PRIMARY KEY），那么InnoDB会选择主键作为聚集索引。</li>
<li>如果没有显示定义主键，则InnoDB会选择第一个不包含由NULL值的唯一索引作为主键索引</li>
<li>如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增</li>
</ol>
<h2 id="4-索引使用原则"><a href="#4-索引使用原则" class="headerlink" title="4.索引使用原则"></a>4.索引使用原则</h2><h3 id="4-1-列的离散度"><a href="#4-1-列的离散度" class="headerlink" title="4.1 列的离散度"></a>4.1 列的离散度</h3><p>列的离散度的公式：count(distinct(column_name)) / count(*)，列的全部不同值和所有数据行的比例。</p>
<p>数据行数相同的情况下，分子越大，列的离散度就越高。</p>
<p>列的重复值越多，离散度就越低，扫描的行数越多，重复值越 少，离散度就越高，扫描的行数越少。</p>
<h3 id="4-2-联合索引最左匹配"><a href="#4-2-联合索引最左匹配" class="headerlink" title="4.2 联合索引最左匹配"></a>4.2 联合索引最左匹配</h3><p>比如在user表中name和phone建立联合索引</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="keyword">user</span> <span class="keyword">ADD</span> INDEX comidx_name_phone(name,phone);</span><br></pre></td></tr></table></figure>
<p><img src="image-20200422095417981.png" alt="image-20200422095417981"></p>
<p>联合索引在B+Tree中是复合的数据结构，是按照从左到右的顺序来建立搜索树的(name在左边，phone在右边)。</p>
<p>从图中看出，name是有序的，phone是无序的，当name相等的时候，phone才是有序的。</p>
<p>我们使用where name=’admin’ AND phone = ‘136xx’去查询数据的时候，B+Tree会优先比较name来确定下一步搜索的方向。如果name相同的时候再比较phone，但是如果查询条件没有name，就不知道第一步应该查那个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。 </p>
<h4 id="4-2-1-什么时候会用到联合索引"><a href="#4-2-1-什么时候会用到联合索引" class="headerlink" title="4.2.1 什么时候会用到联合索引"></a>4.2.1 什么时候会用到联合索引</h4><p><img src="image-20200422100300949.png" alt="image-20200422100300949"></p>
<h3 id="4-3-覆盖索引"><a href="#4-3-覆盖索引" class="headerlink" title="4.3 覆盖索引"></a>4.3 覆盖索引</h3><p>回表：</p>
<p>非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面数据，它比基于主键索引的查询多扫描了一颗索引树。</p>
<p>例如：SELECT * FROM user WHERE name = ‘Qingshan’；</p>
<p><img src="image-20200422100915197.png" alt="image-20200422100915197"></p>
<p>覆盖索引：</p>
<p>​    在辅助索引中，不管是单列索引还是联合索引，如果SELECT的数据列只用从索引中就能够取到，不必从数据区中读取，这个时候使用的索引就是覆盖索引，避免了回表。</p>
<h2 id="5-索引的创建与使用"><a href="#5-索引的创建与使用" class="headerlink" title="5. 索引的创建与使用"></a>5. 索引的创建与使用</h2><p>因为索引对于改善查询性能的作用是巨大的，所以我们的目标尽量使用索引。</p>
<h3 id="5-1-索引的创建"><a href="#5-1-索引的创建" class="headerlink" title="5.1 索引的创建"></a>5.1 索引的创建</h3><ul>
<li>在用于WHERE 判断、ORDER 排序、和JOIN的ON字段上创建索引</li>
<li>索引的个数不要过多<ul>
<li>浪费空间，更新变慢</li>
</ul>
</li>
<li>过长的字段，建立前缀索引</li>
<li>区分度低的字段，例如性别，不要建立索引<ul>
<li>离散度太低，导致扫描行数过多。</li>
</ul>
</li>
<li>频繁更新的值，不要作为主键或者索引<ul>
<li>页分裂</li>
</ul>
</li>
<li>随机无序的值，不建议作为主键索引，例如身份证，UUID<ul>
<li>无序，分裂</li>
</ul>
</li>
<li>组合索引把散列性高（区分度高）的值放在前面</li>
<li>创建复合索引，而不是修改单列索引</li>
</ul>
<h3 id="5-2-什么时候用不到索引？"><a href="#5-2-什么时候用不到索引？" class="headerlink" title="5.2 什么时候用不到索引？"></a>5.2 什么时候用不到索引？</h3><ul>
<li>索引列上使用函数、表达式</li>
<li>字符串不加引号，出现隐式转换</li>
<li>LIKE条件中前面带%</li>
<li>负向查询</li>
</ul>
<p>其实，用不用索引，最终都是优化器说了算。</p>
<p>优化器</p>
<p>基于cost开销（Cost Base Optimizer），它不是基于规则（Rule-Bases Optimizer）也不是基于语义，怎样开销小就怎么来。</p>
<p>使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引，什么情况一定不用索引的规则。</p>


        <hr>
        <!-- Pager -->
        <ul class="pager">
          
          <li class="previous">
            <a href="/cn/java/Lucene/" data-toggle="tooltip" data-placement="top" title="全文检索技术 Lucene">&larr; Previous Post</a>
          </li>
          
          
          <li class="next">
            <a href="/cn/mysql/MySQL体系结构/" data-toggle="tooltip" data-placement="top" title="MySQL体系结构">Next Post &rarr;</a>
          </li>
          
        </ul>

        
        <!-- tip start -->
        <!-- tip -->
<!-- tip start -->
<div class="tip">
  <p>
    
      如果您喜欢此博客或发现它对您有用，则欢迎对此发表评论。 也欢迎您共享此博客，以便更多人可以参与。 如果博客中使用的图像侵犯了您的版权，请与作者联系以将其删除。 谢谢 ！
    
  </p>
</div>
<!-- tip end -->

        <!-- tip end -->
        

        
        <hr>

        <!-- comments start -->
        <!-- 1. gitalk comment -->


<!-- 2. gitment comment -->


<!-- 3. disqus comment -->


        <!-- comments end -->
        <hr>

      </div>

      <!-- Catalog: Tabe of Content -->
      <!-- Table of Contents -->



      <!-- Sidebar Container -->
      <div class="
                col-lg-8 col-lg-offset-1
                col-md-10 col-md-offset-1
                sidebar-container">

        <!-- Featured Tags -->
        
        <section>
          <!-- no hr -->
          <h5>
            <a href="/tags/">特色标签</a>
          </h5>
          <div class="tags">
            
            <a class="tag" href="/tags/#数据库" title="数据库">数据库</a>
            
            <a class="tag" href="/tags/#mysql" title="mysql">mysql</a>
            
          </div>
        </section>
        

        <!-- Friends Blog -->
        
        <hr>
        <h5>链友</h5>
        <ul class="list-inline">

          
        </ul>
        
      </div>
    </div>
  </div>
</article>



<!-- anchorjs start -->
<!-- async load function -->
<!-- anchor-js, Doc:http://bryanbraun.github.io/anchorjs/ -->
<script type="text/javascript">
  // async load function
  function async (u, c) {
    var d = document,
      t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) {
      o.addEventListener('load', function(e) {
        c(null, e);
      }, false);
    }
    s.parentNode.insertBefore(o, s);
  };
</script>
<script type="text/javascript">
  //anchor-js, Doc:http://bryanbraun.github.io/anchorjs/
  async ("https://cdn.bootcss.com/anchor-js/1.1.1/anchor.min.js", function() {
    anchors.options = {
      visible: 'hover',
      placement: 'left',
      // icon: 'ℬ'
      icon: '❡'
    };
    anchors.add().remove('.intro-header h1').remove('.subheading').remove('.sidebar-container h5');
  });
</script>
<style>
  /* place left on bigger screen */
  @media all and (min-width: 800px) {
    .anchorjs-link {
      position: absolute;
      left: -0.75em;
      font-size: 1.1em;
      margin-top: -0.1em;
    }
  }
</style>

<!-- anchorjs end -->



		<!-- Footer (contains ThemeColor、viewer) -->
		<!-- Footer -->
<footer>
  <div class="container">
    <div class="row">
      <div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
        <ul class="list-inline text-center">
          

          

          

          

          

          

          

          

        </ul>
        <p class="copyright text-muted">
          Copyright &copy;
          zhuang
          2021
          <br>
          Theme by
          <a target="_blank" rel="noopener" href="http://beantech.org">BeanTech</a>
          <span style="display: inline-block; margin: 0 5px;">
            <i class="fa fa-heart"></i>
          </span>
          re-Ported by
          <a target="_blank" rel="noopener" href="https://v-vincen.life/">Live My Life</a>
        </p>
      </div>
    </div>
  </div>
</footer>

<a id="rocket" href="#top" class=""></a>


  <!-- jQuery -->
  <script type="text/javascript" src="/js/jquery.min.js"></script>
  <!-- Bootstrap Core JavaScript -->
  <script type="text/javascript" src="/js/bootstrap.min.js"></script>
  <!-- Custom Theme JavaScript -->
  <script type="text/javascript" src="/js/hux-blog.min.js"></script>
  <!-- catalog -->
  <script async="true" type="text/javascript" src="/js/catalog.js"></script>
  <!-- totop(rocket) -->
  <script async="true" type="text/javascript" src="/js/totop.js"></script>

  
    <!-- Busuanzi JavaScript -->
    <script async="async" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
  

  
    <!-- Scroll start -->
    <script async="async" type="text/javascript" src="/js/scroll.js"></script>
    <!-- Scroll end -->
  

  

  

  
    <!-- ribbon -->
    <script type="text/javascript" src="/js/ribbonDynamic.js"></script>
  

  






  <!-- viewer start -->
  <!-- viewer start (Picture preview) -->
  
    <script async="async" type="text/javascript" src="/js/viewer/viewer.min.js"></script>
    <script async="async" type="text/javascript" src="/js/viewer/pic-viewer.js"></script>
  

  <!-- viewer end -->


<script>
  // async load function
  function async (u, c) {
    var d = document,
      t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) {
      o.addEventListener('load', function (e) {
        c(null, e);
      }, false);
    }
    s.parentNode.insertBefore(o, s);
  }

  // fastClick.js
  async ("https://cdn.bootcss.com/fastclick/1.0.6/fastclick.min.js", function () {
    var $nav = document.querySelector("nav");
    if ($nav)
      FastClick.attach($nav);
    }
  )
</script>

<!-- Because of the native support for backtick-style fenced code blocks right within the Markdown is landed in Github Pages, From V1.6, There is no need for Highlight.js, so Huxblog drops it officially. -
https://github.com/blog/2100-github-pages-now-faster-and-simpler-with-jekyll-3-0 - https://help.github.com/articles/creating-and-highlighting-code-blocks/ -->
<!-- <script> async ("http://cdn.bootcss.com/highlight.js/8.6/highlight.min.js", function () { hljs.initHighlightingOnLoad(); }) </script> <link href="http://cdn.bootcss.com/highlight.js/8.6/styles/github.min.css" rel="stylesheet"> -->

<!-- jquery.tagcloud.js -->
<!-- <script> // only load tagcloud.js in tag.html if ($('#tag_cloud').length !== 0) { async ("http://yoursite-url/js/jquery.tagcloud.js", function () { $.fn.tagcloud.defaults = { // size: { start: 1, end: 1, unit: 'em' }, color: {
start: '#bbbbee', end: '#0085a1' } }; $('#tag_cloud a').tagcloud(); }) } </script> -->


		<!-- Search -->
		
		<div class="popup search-popup local-search-popup">
  <span class="popup-btn-close">
    ESC
  </span>
  <div class="container">
    <div class="row">
      <!-- <div class="col-md-9 col-md-offset-1"> -->
      <div class="col-lg-9 col-lg-offset-1 col-md-10 col-md-offset-1 local-search-content">

        <div class="local-search-header clearfix">

          <div class="local-search-input-wrapper">
            <span class="search-icon">
              <i class="fa fa-search fa-lg" style="margin: 25px 10px 25px 20px;"></i>
            </span>
            <input autocomplete="off" placeholder="搜索..." type="text" id="local-search-input">
          </div>
        </div>
        <div id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>


  
    <script src="/js/ziploader.js"></script>
  
  <script>
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.json";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    // monitor main search box;
    var onPopupClose = function (e) {
      $('.popup').fadeOut(300);
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $('.popup').fadeIn(300);
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }
    // get search zip version
    $.get('/searchVersion.json?t=' + (+new Date()), function (res) {
      if (localStorage.getItem('searchVersion') !== res) {
        localStorage.setItem('searchVersion', res);
        initSearchJson();
      }
    });

    function initSearchJson() {
      initLoad(['/search.flv'], {
        loadOptions: {
          success: function (obj) {
            localStorage.setItem('searchJson', obj['search.json'])
          },
          error: function (e) {
            return console.log(e)
          }
        },
        returnOptions: {
          'json': TYPE_TEXT
        },
        mimeOptions: {
          'json': 'application/json'
        }
      })
    }
    // search function;
    var searchFunc = function (search_id, content_id) {
      'use strict';
      isfetched = true;
      var datas = JSON.parse(localStorage.getItem('searchJson'));
      // console.log(search_id)
      var input = document.getElementById(search_id);
      var resultContent = document.getElementById(content_id);
      var inputEventFunction = function () {
        var searchText = input.value.trim().toLowerCase();
        var keywords = searchText.split(/[\s\-]+/);
        if (keywords.length > 1) {
          keywords.push(searchText);
        }
        var resultItems = [];
        if (searchText.length > 0) {
          // perform local searching
          datas.forEach(function (data) {
            var isMatch = false;
            var hitCount = 0;
            var searchTextCount = 0;
            var title = data.title
              ? data.title.trim()
              : '';
            var titleInLowerCase = title.toLowerCase();
            var content = data.content
              ? data.content.trim().replace(/<[^>]+>/g, "")
              : '';
            var contentInLowerCase = content.toLowerCase();
            var articleUrl = decodeURIComponent(data.url);

            var date = data.date;
            var dateTime = date.replace(/T/, " ").replace(/.000Z/, "");
            var imgUrl = data.header_img;
            


            var indexOfTitle = [];
            var indexOfContent = [];
            // only match articles with not empty titles
            keywords.forEach(function (keyword) {
              function getIndexByWord(word, text, caseSensitive) {
                var wordLen = word.length;
                if (wordLen === 0) {
                  return [];
                }
                var startPosition = 0,
                  position = [],
                  index = [];
                if (!caseSensitive) {
                  text = text.toLowerCase();
                  word = word.toLowerCase();
                }
                while ((position = text.indexOf(word, startPosition)) > -1) {
                  index.push({position: position, word: word});
                  startPosition = position + wordLen;
                }
                return index;
              }
              indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
              indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
            });
            if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
              isMatch = true;
              hitCount = indexOfTitle.length + indexOfContent.length;
            }
            // show search results
            if (isMatch) {
              // sort index by position of keyword
              [indexOfTitle, indexOfContent].forEach(function (index) {
                index.sort(function (itemLeft, itemRight) {
                  if (itemRight.position !== itemLeft.position) {
                    return itemRight.position - itemLeft.position;
                  } else {
                    return itemLeft.word.length - itemRight.word.length;
                  }
                });
              });
              // merge hits into slices
              function mergeIntoSlice(text, start, end, index) {
                var item = index[index.length - 1];
                var position = item.position;
                var word = item.word;
                var hits = [];
                var searchTextCountInSlice = 0;
                while (position + word.length <= end && index.length != 0) {
                  if (word === searchText) {
                    searchTextCountInSlice++;
                  }
                  hits.push({position: position, length: word.length});
                  var wordEnd = position + word.length;
                  // move to next position of hit
                  index.pop();
                  while (index.length != 0) {
                    item = index[index.length - 1];
                    position = item.position;
                    word = item.word;
                    if (wordEnd > position) {
                      index.pop();
                    } else {
                      break;
                    }
                  }
                }
                searchTextCount += searchTextCountInSlice;
                return {hits: hits, start: start, end: end, searchTextCount: searchTextCountInSlice};
              }
              var slicesOfTitle = [];
              if (indexOfTitle.length != 0) {
                slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
              }
              var slicesOfContent = [];
              while (indexOfContent.length != 0) {
                var item = indexOfContent[indexOfContent.length - 1];
                var position = item.position;
                var word = item.word;
                // cut out 100 characters
                var start = position - 20;
                var end = position + 80;
                if (start < 0) {
                  start = 0;
                }
                if (end < position + word.length) {
                  end = position + word.length;
                }
                if (end > content.length) {
                  end = content.length;
                }
                slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
              }
              // sort slices in content by search text's count and hits' count
              slicesOfContent.sort(function (sliceLeft, sliceRight) {
                if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                  return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                  return sliceRight.hits.length - sliceLeft.hits.length;
                } else {
                  return sliceLeft.start - sliceRight.start;
                }
              });
              // select top N slices in content
              var upperBound = parseInt('1');
              if (upperBound >= 0) {
                slicesOfContent = slicesOfContent.slice(0, upperBound);
              }
              // highlight title and content
              function highlightKeyword(text, slice) {
                var result = '';
                var prevEnd = slice.start;
                slice.hits.forEach(function (hit) {
                  result += text.substring(prevEnd, hit.position);
                  var end = hit.position + hit.length;
                  result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                  prevEnd = end;
                });
                result += text.substring(prevEnd, slice.end);
                return result;
              }
              var resultItem = '';

              // if (slicesOfTitle.length != 0) {   resultItem += "<li><a target='_blank' href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>"; } else {   resultItem += "<li><a target='_blank' href='" +
              // articleUrl + "' class='search-result-title'>" + title + "</a>"; } slicesOfContent.forEach(function (slice) {   resultItem += "<a target='_blank' href='" + articleUrl + "'><p class=\"search-result\">" + highlightKeyword(content, slice) +
              // "...</p></a>"; }); resultItem += "</li>";

              if (slicesOfTitle.length != 0) {
                resultItem += "<a target='_blank' href='" + articleUrl + "' class='search-result'><div class='search-result-left'><div class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</div><time class='search-result-date'>" + dateTime + "</time>";
              } else {
                resultItem += "<a target='_blank' href='" + articleUrl + "' class='search-result'><div class='search-result-left'><div class='search-result-title'>" + title + "</div><time class='search-result-date'>" + dateTime + "</time>";
              }
              slicesOfContent.forEach(function (slice) {
                resultItem += "<p class=\"search-result-content\">" + highlightKeyword(content, slice) + "...</p>";
              });
              resultItem += "</div><div class='search-result-right'><img class='media-image' src='" + imgUrl + "' width='64px' height='48px'></img></div></a>";

              resultItems.push({item: resultItem, searchTextCount: searchTextCount, hitCount: hitCount, id: resultItems.length});
            }
          })
        };

        if (keywords.length === 1 && keywords[0] === "") {
          resultContent.innerHTML = '<div id="no-result"></div>'
        } else if (resultItems.length === 0) {
          resultContent.innerHTML = '<div id="no-result"></div>'
        } else {
          resultItems.sort(function (resultLeft, resultRight) {
            if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
              return resultRight.searchTextCount - resultLeft.searchTextCount;
            } else if (resultLeft.hitCount !== resultRight.hitCount) {
              return resultRight.hitCount - resultLeft.hitCount;
            } else {
              return resultRight.id - resultLeft.id;
            }
          });
          var searchResultList = '<div class=\"search-result-list\">';
          resultItems.forEach(function (result) {
            searchResultList += result.item;
          })
          searchResultList += "</div>";
          resultContent.innerHTML = searchResultList;
        }
      }
      if ('auto' === 'auto') {
        input.addEventListener('input', inputEventFunction);
      } else {
        $('.search-icon').click(inputEventFunction);
        input.addEventListener('keypress', function (event) {
          if (event.keyCode === 13) {
            inputEventFunction();
          }
        });
      }
      // remove loading animation
      $('body').css('overflow', '');
      proceedsearch();
    }
    // handle and trigger popup window;
    $('.popup-trigger').click(function (e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc('local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });
    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function (e) {
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 && $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });

    document.addEventListener('mouseup', (e) => {
      var _con = document.querySelector(".local-search-content");
      if (_con) {
        if (!_con.contains(e.target)) {
          onPopupClose();
        }
      }
    });
  </script>


		
	<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        tex2jax: {
            inlineMath: [ ["$","$"], ["\\(","\\)"] ],
            skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
            processEscapes: true
        }
    });
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax();
        for (var i = 0; i < all.length; ++i)
            all[i].SourceElement().parentNode.className += ' has-jax';
    });
</script>
<script src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
</body>
</html>
